﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;

namespace Project
{
    public partial class FrmMergeExecel : Form
    {
        DataSet dataDs;
        string strConn = string.Empty;
        public FrmMergeExecel()
        {
            InitializeComponent();
        }

        public void MergeExecel()
        {
            string path = @"C:\Users\Administrator\Desktop\新建文件夹\姜羽4.19工作日志.xlsx";
            string path2 = @"C:\Users\Administrator\Desktop\新建文件夹\姜羽4.19工作日志 - 副本.xlsx";
            string path1 = @"C:\Users\Administrator\Desktop\新建文件夹\姜羽4.20工作日志.xlsx";
            string pathEndExecel = @"C:\Users\Administrator\Desktop\end.xlsx";

            dataDs = ExcelToDS(path);
            dataDs.Merge(ExcelToDS(path2).Copy());
            dataDs.Merge(ExcelToDS(path1));

            dataDs.Tables[1].Columns.Remove("F20");
            dataDs.Tables[1].Columns.Remove("F21");
            //DSToExcel(path1, ds);
            WriteToExecelByDs(pathEndExecel, dataDs);

        }

        public DataSet ExcelToDS(string Path)
        {
            //Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            //Workbook wb = app.Workbooks.Add(Path);
            //Worksheet ws = app.Worksheets["查勘任务流"] as Worksheet;

            //Microsoft.Office.Interop.Excel.Range range1 = ws.get_Range("A1", "A65535");
            //range1.NumberFormat = "@";//文本格式

            //Worksheet ws2 = app.Worksheets["定损任务流"] as Worksheet;
            //Microsoft.Office.Interop.Excel.Range range2 = ws2.get_Range("A1", "A65535");
            //range2.NumberFormat = "@";//文本格式
            //wb.Save();

            //string strConn = "Provider =Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            //string strConn = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 12.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = new DataSet();
            strExcel = "select * from [定损任务流$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds, "Table1");

            myCommand = new OleDbDataAdapter("select * from [查勘任务流$]", strConn);
            myCommand.Fill(ds, "Table2");

            ds.Tables[1].Columns.Remove("F20");
            ds.Tables[1].Columns.Remove("F21");
            ds.Tables[0].Columns.Remove("F10");
            ds.Tables[0].Columns.Remove("F11");
            removeEmpty(ds.Tables[0]);
            removeEmpty(ds.Tables[1]);
            //app.Quit();
            myCommand.Dispose();
            conn.Close();
            return ds;
        }

        public void DSToExcel(string Path, DataSet oldds)
        {
            //先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构 
            //string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0";
            //string strConn = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 12.0;";
            OleDbConnection myConn = new OleDbConnection(strConn);
            string strCom = "select * from [Sheet3$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
            //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。 
            builder.QuotePrefix = "[";     //获取insert语句中保留字符（起始位置） 
            builder.QuoteSuffix = "]"; //获取insert语句中保留字符（结束位置） 
            DataSet newds = new DataSet();
            myCommand.Fill(newds, "Table1");
            removeEmpty(newds.Tables[0]);
            for (int i = 0; i < oldds.Tables[0].Rows.Count; i++)
            {
                //在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
                //在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState != Added
                DataRow nrow = newds.Tables["Table1"].NewRow();
                for (int j = 0; j < newds.Tables[0].Columns.Count; j++)
                {
                    nrow[j] = oldds.Tables[0].Rows[i][j];
                }
                newds.Tables["Table1"].Rows.Add(nrow);
            }

            myCommand.Update(newds, "Table1");
            myConn.Close();
        }

        public void WriteToExecelByDs(string filePath, DataSet ds)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            System.Data.DataTable excelTable = new System.Data.DataTable();
            System.Data.DataTable excelTable2 = new System.Data.DataTable();
            removeEmpty(ds.Tables[0]);
            removeEmpty(ds.Tables[1]);
            excelTable = ds.Tables["Table1"];
            excelTable2 = ds.Tables["Table2"];
            try
            {
                app.Visible = false;
                Workbook wb = app.Workbooks.Add(true);

                AddSheet(wb, "查勘任务流");

                #region 定损任务流

                Worksheet ws = app.Worksheets[1] as Worksheet;

                Microsoft.Office.Interop.Excel.Range range1 = ws.get_Range("B1", "B65535");
                range1.NumberFormat = "@";//文本格式

                if (excelTable.Rows.Count > 0)
                {
                    int row = 0;
                    row = excelTable.Rows.Count;
                    int col = excelTable.Columns.Count;
                    for (int i = 0; i < row; i++)
                    {
                        for (int j = 0; j < col; j++)
                        {
                            string str = excelTable.Rows[i][j].ToString();
                            ws.Cells[i + 2, j + 1] = str;
                        }
                    }
                }

                int size = excelTable.Columns.Count;
                for (int i = 0; i < size; i++)
                {
                    if (!excelTable.Columns[i].ColumnName.Contains("F"))
                    {
                        ws.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
                    }
                }

                ws.UsedRange.EntireColumn.AutoFit();
                ws.Name = "定损任务流";

                #endregion

                #region 查勘任务流

                ws = app.Worksheets["查勘任务流"] as Worksheet;

                if (excelTable2.Rows.Count > 0)
                {
                    int row = 0;
                    row = excelTable2.Rows.Count;
                    int col = excelTable2.Columns.Count;
                    for (int i = 0; i < row; i++)
                    {
                        for (int j = 0; j < col; j++)
                        {
                            string str = excelTable2.Rows[i][j].ToString();
                            ws.Cells[i + 2, j + 1] = str;
                        }
                    }
                }

                size = excelTable2.Columns.Count;
                for (int i = 0; i < size; i++)
                {
                    if (!excelTable2.Columns[i].ColumnName.Contains("F"))
                    {
                        ws.Cells[1, 1 + i] = excelTable2.Columns[i].ColumnName;
                    }
                }

                ws.UsedRange.EntireColumn.AutoFit();
                #endregion


                //不显示保存和覆盖的确认提示框: 
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                //保存工作簿 
                wb.SaveCopyAs(filePath);  //如果用wb.save()就会报错，提示Sheet1.xls无法访问; 保存时如果文件不存在也会报错;
                //保存excel文件 
                //app.SaveWorkspace(filePath);
                app.Quit();
                app = null;
            }
            catch (Exception ex)
            {
            }
        }

        protected void removeEmpty(System.Data.DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool rowdataisnull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {

                        rowdataisnull = false;
                    }

                }
                if (rowdataisnull)
                {
                    removelist.Add(dt.Rows[i]);
                }

            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
        }

        public Excel._Worksheet AddSheet(Excel._Workbook xBk, string sheetName)
        {
            Excel.Worksheet sheet = (Excel.Worksheet)xBk.Sheets.get_Item(xBk.Sheets.Count);
            Excel._Worksheet xSt1 = (Microsoft.Office.Interop.Excel._Worksheet)xBk.Sheets.Add
      (Missing.Value, sheet, Missing.Value, Missing.Value);
            xSt1.Name = sheetName;
            xSt1.Activate();
            return xSt1;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog op = new FolderBrowserDialog();
            op.ShowDialog();
            this.textBox1.Text = op.SelectedPath;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.textBox2.Text += "开始合并\r\n";
            string path = string.Empty;
            try
            {
                if (Directory.Exists(this.textBox1.Text))
                {
                    DirectoryInfo TheFolder = new DirectoryInfo(this.textBox1.Text);
                    FileInfo[] files = TheFolder.GetFiles();
                    this.textBox2.Text += "共发现" + files.Length + "个EXECEL\r\n";

                    for (int i = 0; i < files.Length; i++)
                    {
                        this.textBox2.Text += "正在获取第" + (i + 1) + "个EXECEL\r\n";
                        this.textBox2.Select(this.textBox2.TextLength, 0);
                        //滚动到控件光标处 
                        this.textBox2.ScrollToCaret();

                        path = files[i].FullName;
                        if (".XLS".Equals(files[i].Extension.ToUpper()))
                        {
                            strConn = "Provider =Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 8.0;IMEX=1'";
                        }
                        else if (".XLSX".Equals(files[i].Extension.ToUpper()))
                        {
                            strConn = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + path + ";" + "Extended Properties='Excel 12.0;IMEX=1'";
                        }
                        else
                        {
                            continue;
                        }
                        if (dataDs == null)
                        {
                            dataDs = ExcelToDS(path);
                        }
                        else
                        {
                            dataDs.Merge(ExcelToDS(path), false, MissingSchemaAction.Add);
                        }
                    }
                    this.textBox2.Text += "正在合并数据，此过程比较耗时请等待。。\r\n";
                    this.textBox2.Select(this.textBox2.TextLength, 0);
                    //滚动到控件光标处 
                    this.textBox2.ScrollToCaret();

                    string execelPath = @"C:\Users\Administrator\Desktop\end" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
                    WriteToExecelByDs(execelPath, dataDs);
                    this.textBox2.Text += "合并成功";
                    MessageBox.Show("EXECEL合并成功，已保存至 " + execelPath);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(path + "可能存在格式问题无法合并。");
            }
        }


    }
}
